package tw.com.test01;


import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class SQLite{
	final private static int _DB_VERSION = 1;
    final private static String _DB_DATABASE_NAME = "test01.db";
	private SQLiteDatabase db;
	private Context context;

	public SQLite(Context context) {
		this.context = context;
	}

	public SQLiteDatabase open(){
		Helper h =new Helper(context,_DB_DATABASE_NAME,null,_DB_VERSION);
		this.db= h.getWritableDatabase();
//		h.onCreate(db);
		return (db);
	}
	public void close(){
	//db.execSQL("DROP TABLE IF EXISTS Subject");
		db.close();

	}
	
	Cursor  query(String sql){
		 return db.rawQuery(sql,null);	
	}
	public void execSql(String sql){
		 db.execSQL(sql);	
	}
	
}

class Helper extends SQLiteOpenHelper{
	public Helper(Context context) {
		// TODO Auto-generated constructor stub
		this(context,  null, 1); 
	}
	
	public Helper(Context context, CursorFactory factory,int version) {
		this(context, null, factory, version);
		// TODO Auto-generated constructor stub
	}

	public Helper(Context context,String dbPath, CursorFactory factory,int version) {
		super(context, dbPath, factory, version);
		// TODO Auto-generated constructor stub
	}
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
        System.out.print("DbHelper:onCreate()");
        db.execSQL("DROP TABLE IF EXISTS Subject");
        db.execSQL(
                "CREATE TABLE Subject (" +
                        " Subject_ID char(8) PRIMARY KEY, " +
                        " Subject_Description VARCHAR(50) NOT NULL" +
                ")"
        );
        
        db.execSQL("insert into Subject values('公民','公民');");
        db.execSQL("insert into Subject values('憲法','憲法');");
        db.execSQL("insert into Subject values('法學緒論','法學緒論');");
        db.execSQL("insert into Subject values('行政學','行政學')");
        
        System.out.print("DbHelper:Create Table [Subject]");

        db.execSQL("DROP TABLE IF EXISTS Paper");
        db.execSQL(
                "CREATE TABLE Paper (" +
                        " Subject_ID char(8)  " +
                        " ,Paper_ID char(8)  " +
                        " ,Paper_Description VARCHAR(50) NOT NULL" +
                        " ,Level Integer NOT NULL" +
                        " ,Score Integer NOT NULL" +
                        " ,Year Integer NOT NULL" +
                        " , PRIMARY KEY (SUbject_id,Paper_ID)" +
                ")"
        );
        db.execSQL("DROP TABLE IF EXISTS Question");
        db.execSQL(
                "CREATE TABLE Question (" +
                        " Subject_ID char(8)  " +
                        " ,Paper_ID char(8)  " +
                        " ,QNO char(3)" +
                        " ,Question VARCHAR(1024) NOT NULL" +
                        " ,Anser char(1) NOT NULL" +
                        " ,ChoiceA char(1) not null default ''" +
                        " ,ChoiceB char(1) not null default ''" +
                        " ,ChoiceC char(1) not null default ''" +
                        " ,ChoiceD char(1) not null default ''" +
                        " ,Exclude char(1) not null default ''" +	//
                        " ,Count_Total Integer default 0" +
                        " ,Count_Wrong Integer default 0" +
                        " , PRIMARY KEY (SUbject_id,Paper_ID,QNO)" +
                ")"
        );
        
	}
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS Subject");
        onCreate(db);		
	}		
}
